﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_MGABEW(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_MGABEW(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryICC_MGABEW(dwData, 1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_MGABEW(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertICC_MGABEW(dwLocal);
            }
            UpdateICC_MGABEW(dwLocal);
        }
        public void InsertICC_MGABEW(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            //GSCMANDANT  GSCART GSCVAR GSCFARBE 
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO ICC_MGABEW "
            + " SELECT * FROM GABEW WHERE GABMANDANT=:GABMANDANT: AND  SUBSTR(GABBUCHDAT,1,6)=:GABBUCHDAT:";
            or1.CommandText = InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DeleteICC_MGABEW(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM  ICC_MGABEW WHERE "
                            + "  GABMANDANT=:GABMANDANT: ";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM  ICC_MGABEW WHERE "
                            + "  GABMANDANT=:GABMANDANT: AND GABBS IN ('61','62','42','43') ";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = "DELETE FROM  ICC_MGABEW WHERE "
                            + "  GABMANDANT=:GABMANDANT: AND GABBS IN ('20','21','22')  AND GABAUFTYP='V' AND GABLO<>'FI' ";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                case 4:
                    or1.CommandText = "DELETE FROM  ICC_MGABEW WHERE "
                            + "  GABMANDANT=:GABMANDANT: AND GABBS IN ('20','21','22')  AND GABLO='FI' AND GABLB NOT IN ( 'PR')";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break; 
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_MGABEW(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    break;
                case 2:
                    or1.CommandText = "SELECT ABPART,ABPVAR,ABPFARBE,GABLO,GABLB,GABAUFTYP,GABAUFKRS,GABAUFLFD,GABAUFPOS,M.GABBS,MAX(S.SCN) AS SCN,SUM(GABKG) AS GABKG"
                    + " FROM ICC_MGABEW M LEFT JOIN ABPOS P ON GABMANDANT=ABPMANDANT AND GABAUFKRS=ABPAUFKRS AND GABAUFLFD= ABPAUFLFD AND GABAUFPOS=ABPAUFPOS "
                    + " LEFT JOIN ICC_GABBS S ON M.GABBS=S.GABBS WHERE  "
                            + "  GABMANDANT=:GABMANDANT AND GABLO=:GABLO AND  GABART=:GABART AND GABVAR=:GABVAR "
                            + " GROUP BY  ABPART,ABPVAR,ABPFARBE,GABLO,GABLB,GABAUFTYP,GABAUFKRS,GABAUFLFD,GABAUFPOS,M.GABBS ";
                    or1.Parameters.Add("GABMANDANT", OracleType.NVarChar).Value = dwData["GABMANDANT"].ToString();
                    or1.Parameters.Add("GABLO", OracleType.NVarChar).Value = dwData["GABLO"].ToString();
                    or1.Parameters.Add("GABART", OracleType.NVarChar).Value = dwData["GABART"].ToString();
                    or1.Parameters.Add("GABVAR", OracleType.NVarChar).Value = dwData["GABVAR"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT  ABPART,ABPVAR,ABPFARBE,GABLO,GABLB,GABART,GABVAR,GABFARBE,GABAUFTYP,GABAUFKRS,GABAUFLFD,GABAUFPOS,M.GABBS,MAX(S.SCN) AS SCN,SUM(GABKG) AS GABKG"
                    + " FROM ICC_MGABEW M LEFT JOIN ABPOS P ON GABAUFTYP='V' AND GABMANDANT=ABPMANDANT AND GABAUFKRS=ABPAUFKRS AND GABAUFLFD= ABPAUFLFD AND GABAUFPOS=ABPAUFPOS "
                    + " LEFT JOIN ICC_GABBS S ON M.GABBS=S.GABBS"
                    + " WHERE GABMANDANT=:GABMANDANT AND GABLO=:GABLO AND ABPART=:GABART"
                    + " GROUP BY  ABPART,ABPVAR,ABPFARBE,GABLO, GABLB,GABART,GABVAR,GABFARBE,GABAUFTYP,GABAUFKRS,GABAUFLFD,GABAUFPOS,M.GABBS ";
                    or1.Parameters.Add("GABMANDANT", OracleType.NVarChar).Value = dwData["GABMANDANT"].ToString();
                    or1.Parameters.Add("GABLO", OracleType.NVarChar).Value = dwData["GABLO"].ToString();
                    or1.Parameters.Add("GABART", OracleType.NVarChar).Value = dwData["GABART"].ToString();
                    break;
                case 4:
                    or1.CommandText = "SELECT COUNT(*) FROM ICC_MGABEW WHERE  "
                            + "  GABMANDANT=:GABMANDANT: AND RTRIM(GABBUCHDAT)=:GABBUCHDAT:";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "ICC_MGABEW";
            }

            setReturn.Merge(Result);
            return setReturn;
        }
        public void UpdateICC_MGABEW(DataRow dwData, int option = 1)
        {
            
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                case 2:
                    or1.CommandText = "UPDATE ICC_MGABEW  SET GABBUCHDAT=:GABBUCHDAT: WHERE  "
                            + "  GABMANDANT=:GABMANDANT: ";
                    or1.CommandText = InjectData(or1.CommandText, dwData);
                    break;
            }

            Connect2Proxy(or1);
        }
    }
}
